package com.ymttest.database.annotation.product;

import com.shop2cn.iapi.agentproducts.parameter.AgentProductComplexInfo;
import com.ymt.core.tool.FormatUtil;
import com.ymttest.database.annotation.SqlMapperFactory;
import com.ymttest.database.mapping.agentrebate.Ymt_AgentRebateSqlProvider;
import com.ymttest.database.mapping.agentrebate.Ymt_ConditionRebateSqlProvider;
import com.ymttest.database.mapping.agentrebate.Ymt_ConditionRebateValueSqlProvider;
import com.ymttest.database.mapping.agentrebate.Ymt_ConditionTypeSqlProvider;
import com.ymttest.database.mapping.agentrebate.Ymt_ProductSelectionConditionSqlProvider;
import com.ymttest.database.model.agentrebate.Ymt_AgentRebate;
import com.ymttest.database.model.agentrebate.Ymt_AgentRebateExample;
import com.ymttest.database.model.agentrebate.Ymt_ConditionRebate;
import com.ymttest.database.model.agentrebate.Ymt_ConditionRebateExample;
import com.ymttest.database.model.agentrebate.Ymt_ConditionRebateValue;
import com.ymttest.database.model.agentrebate.Ymt_ConditionRebateValueExample;
import com.ymttest.database.model.agentrebate.Ymt_ConditionType;
import com.ymttest.database.model.agentrebate.Ymt_ConditionTypeExample;
import com.ymttest.database.model.agentrebate.Ymt_ProductSelectionCondition;
import com.ymttest.database.model.agentrebate.Ymt_ProductSelectionConditionExample;

import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;
import java.util.Map;

public interface IntergratedProductMapper {
	@Select("SELECT * FROM Ymt_SellerProductTag  WHERE sellerid =#{sellerid} AND thirdcategoryid=#{thirdcategoryid} And status=0 order by lastusetime desc")
	public List<Map> selectYmtSellerProductTag(@Param("sellerid")Integer sellerid,@Param("thirdcategoryid")Integer thirdcategoryid);
	@Select("SELECT * FROM Ymt_SellerProductTag  WHERE id =#{id}")
	public List<Map> selectYmtSellerProductTagById(@Param("id")Integer id);
	@Select("SELECT * FROM Ymt_SellerProductTag  WHERE sellerid =#{sellerid} AND CategoryId=#{cid} And status=0 order by lastusetime desc")
	public List<Map> selectYmtSellerProductTagByCategoryId(@Param("sellerid")Integer sellerid,@Param("cid")Integer cid);
	
	@Select("SELECT * FROM Ymt_ProductTag  WHERE productid =#{pid}")
	public List<Map> selectYmtProductTag(@Param("pid")String pid);
	/**
	 * 查询神器商品
	 * @param productId
	 * @return
	 */
	@Select("select * from ymt_AgentProducts where productid = #{productId}")
	public Map<String,Object> selectAgentProductsByProdId(@Param("productId")String productId);
	
	/**
	 * 码头商品表
	 * @param productId
	 * @return
	 */
	@Select("select * from ymt_products where sproductid = #{productId}")
	public Map<String,Object> selectProductByProdId(@Param("productId")String productId);

	/**
	 * 规格
	 * @param productId
	 * @return
	 */
	@Select("select * from ymt_catalogs where sproductid = #{productId}")
	public List<Map<String,Object>> selectCatalogsByProdId(@Param("productId")String productId);
	/**
	 * 规格
	 * @param catalogId
	 * @return
	 */
	@Select("select * from ymt_catalogs where scatalogId = #{catalogId}")
	public Map<String,Object> selectCatalogsByCatalogId(@Param("catalogId")String catalogId);
	
	
	/**
	 * 有效商品图片
	 * @param productId
	 * @return
	 */
	@Select("select * from Ymt_ProductPicture where sproductid = #{productId} and iaction>-1" )
	public List<Map<String,Object>> selectPictureByProdIdAndAction0(@Param("productId")String productId);
	
	
	/**
	 * 商品图文详情 文描
	 * @param productId
	 * @param sellerId
	 * @return
	 */
	@Select("select * from Ymt_ProductDescConfigWord where ProductID = #{productId} and UserID = #{sellerId}" )
	public List<Map<String,Object>> selectDescConfigWordByProdIdAndSellerId(@Param("productId")String productId,@Param("sellerId")Integer sellerId);
	
	
	/**
	 * 商品图文详情 图片
	 * @param productId
	 * @param sellerId
	 * @return
	 */
	@Select("select * from Ymt_ProductDescConfigPicture where ProductID = #{productId} and UserID = #{sellerId} and iaction=0" )
	public List<Map<String,Object>> selectDescConfigPictureByProdIdAndSellerId(@Param("productId")String productId,@Param("sellerId")Integer sellerId);
	
	
	/**
	 * 商品文描模板
	 * @param productId
	 * @return
	 */
	@Select("select * from Ymt_ProductDescMap where productid = #{productId}")
	public List<Map<String,Object>> selectDescMapPByProdId(@Param("productId")String productId);
	
	
	/**
	 * 商品的视频
	 * @param productId
	 * @param sellerId
	 * @return
	 */
	@Select("select * from Ymt_ProductVideo where productid = #{productId} and sellerId = #{sellerId} and action=0")
	public List<Map<String,Object>> selectProdVideoByProdId(@Param("productId")String productId,@Param("sellerId")Integer sellerId);
	
	
	/**
	 * 属性
	 * @param productId
	 * @return
	 */
	@Select("select * from Ymt_ProductProperty where productid = #{productId} and status = 0")
	public List<Map<String,Object>> selectPropertyByProdId(@Param("productId")String productId);

	@Select("select * from Ymt_ProductPspInfo where ProductId = #{productId}")
	public List<Map<String,Object>> selectPspInfoProdId(@Param("productId")String productId);
	
	/**
	 * 商品运费模板
	 * @param productId
	 * @param tplId
	 * @return
	 */
	@Select("select * from Ymt_ProductDeliveryTemplate where productid = #{productId} and templateId = #{tplId} and status = 1")
	public List<Map<String,Object>> selectDeliveryTplByProductIdAndTplId(@Param("productId")String productId,@Param("tplId")Integer tplId);
	
	//套装规格
	@Select("select * from Ymt_SubCatalogs where catalogId = #{catalogId}")
	public List<Map<String,Object>> selectSubCatalogByCatalogId(@Param("catalogId")String catalogId);
	 //规格属性
	@Select("select a.sProductProperty,b.sCategoryAttribute,b.sPicUrl,b.SubType,a.sProductId from Ymt_CatalogProperty as a left join Ymt_CatalogSetAttributes as b on a.sCatalogPropertyId = b.sCatalogPropertyId  where a.sCatalogId =#{catalogId}")
	public List<Map<String,Object>> selectCatalogProAndValByCatalogId(@Param("catalogId")String catalogId);
	
	//套装规格
	@Select("select * from Ymt_SubCatalogs where SubCatalogId =#{subCatalogId}")
	public List<Map<String,Object>> selectSubCatalogBySubCatalogId(@Param("subCatalogId") int subCatalogId);

	@Select("select * from Spu_Product where Status = 0 order by AddTime desc")
	public List<Map<String,Object>> selectSpuProduct();
	/**
	 * 预订商品数
	 * @return
	 */
	@Select("SELECT COUNT (DISTINCT p.sProductId) as total FROM Ymt_Products p INNER JOIN Ymt_Catalogs c  ON p.sProductId = c.sProductId WHERE p.iAction >- 1 AND c.iAction !=- 1 AND c.isPreSale = 1 AND p.iUserId = #{userId} and p.validEnd>getDate()")
	public Map<String,Object> selectUserPreSaleCount(@Param("userId") int userId);
	
	
	/**
	 * SPU属性
	 * @param thirdCategoryId
	 * @return
	 */
	@Select("select * from Spu_CategoryProperty a left join Spu_Property b on a.PropertyId = b.PropertyId where a.ThirdCategoryId = #{thirdCategoryId}")
	public List<Map<String,Object>> selectSpuPropertyByThirdCategoryId(@Param("thirdCategoryId") int thirdCategoryId);
	
	
	@Select("select * from Spu_PropertyValue where PropertyId = #{propertyId}")
	public List<Map<String,Object>> selectSpuPropertyValByPropId(@Param("propertyId") int propertyId);
	
	@Select("select * from Spu_Product where SpuId = #{spuId}")
	public Map<String,Object> selectSpuProductBySpuId(@Param("spuId") int spuId);
	
	
	/**
	 * 活动商品
	 * @param productId
	 * @return
	 */
	@Select("select * from Ymt_ProductsInActivity where sProductId = #{productId}")
	public List<Map<String,Object>> selectActivityProdByProdId(@Param("productId") String productId);
	
	/**
	 * 活动商品
	 * @param activityId
	 * @return
	 */
	@Select("select * from Ymt_Activity where iActivityId = #{activityId}")
	public Map<String,Object> selectActivityByActivityId(@Param("activityId") int activityId);
	//old
	@Select("select * from Ymt_productsInLive  where  iActivityId =#{liveId}")
	public List<Map<String,Object>> selectLiveProdLiveId(@Param("liveId") int liveId);
	//old
	@Select("select * from Ymt_productsInLive where sProductId = #{productId} and iActivityId = #{liveId}")
	public List<Map<String,Object>> selectLiveProdByProdIdAndLiveId(@Param("productId")String productId,@Param("liveId")Integer liveId);
	//old
	@Select("SELECT iSellStatus,case when a.StockNum>0 then 1 else 0 end as 'IsSoldout',COUNT(1) as 'DataCount' FROM "
			+ "( SELECT i.iSellStatus,i.sProductId,SUM(c.iNum) as StockNum FROM Ymt_ProductsInLive i JOIN Ymt_Catalogs c  ON c.sProductId=i.sProductId  AND c.iAction>-1 "
			+ "WHERE i.iActivityId=#{liveId} AND i.iUserId= #{userId} AND i.iAction>-1 group by i.iSellStatus,i.sProductId) "
			+ "AS a group by iSellStatus,case when a.StockNum>0 then 1 else 0 end")
	public List<Map<String,Object>> selectLiveProductTabCount(@Param("userId")int userId,@Param("liveId")Integer liveId);
	//品牌品类
	@Select("select * from Ymt_ProductBrand")
	public List<Map<String,Object>> selectAllBrands();
	
//	@Select("select * from YMT_CategoryTag where CategoryId = #{CategoryId}  AND status=0")
	@Select("SELECT * from dbo.YMT_CategoryTag LEFT JOIN YMT_TagConfig ON YMT_TagConfig.TagId=YMT_CategoryTag.TagId WHERE YMT_CategoryTag.CategoryId=#{CategoryId}  AND YMT_CategoryTag.status=0 AND YMT_TagConfig.status=0")
	public List<Map<String,Object>> selectYMTCategoryTag(@Param("CategoryId")Integer CategoryId);
	
	/**
	 * 类目
	 * @param iCategoryId
	 * @return
	 */
	@Select("select * from Ymt_ProductCategory where iCategoryId = #{iCategoryId}")
	public List<Map<String,Object>> selectYmtProductCategory(@Param("iCategoryId")Integer iCategoryId);
	
	/**
	 * 类目
	 * @param iCategoryId
	 * @return
	 */
	@Delete("delete from Ymt_ProductCategory where iCategoryId = #{iCategoryId}")
	public int deleteYmtProductCategory(@Param("iCategoryId")Integer iCategoryId);
	
	
	/**
	 * 品牌
	 * @param brandEn
	 * @return
	 */
	@Select("select * from Ymt_ProductBrand where sBrandEn = #{brandEn}")
	public List<Map<String,Object>> selectBrandsByBrandEn(@Param("brandEn")String brandEn);
	@Select("SELECT * from dbo.YMT_TagConfig WHERE tagid=#{tagid}")
	public List<Map<String,Object>> selectTagConfig(@Param("tagid")String tagid);
	/**
	 * 品牌Tag信息
	 * @return
	 */
	@Select("SELECT * from dbo.YMT_CategoryTag AS ct  Inner JOIN YMT_TagConfig AS tc ON ct.TagId=tc.TagId WHERE ct.Status=0 AND tc.Status=0 ")
	public List<Map<String,Object>> selectAllTagInfo();
	@Select("SELECT * from dbo.YMT_CategoryTag WHERE CategoryId=#{CategoryId}")
	public List<Map<String,Object>> selectYMTCategoryTag2(@Param("CategoryId")Integer CategoryId);
	
	@Delete("delete from dbo.YMT_CategoryTag WHERE CategoryId=#{CategoryId}")
	public int deleteYMTCategoryTag(@Param("CategoryId")Integer CategoryId);
	
	@Select("select * from Ymt_ProductBrand where iBrandId = #{brandId}")
	public Map<String,Object> selectBrandByBrandId(@Param("brandId")int brandId);
	@Select("select * from YMT_ProductBrandTag where BrandId = #{brandId}")
	public List<Map<String,Object>> selectBrandTagByBrandId(@Param("brandId")int brandId);
	/**
	 * 商家可用品牌
	 * @param brandEn
	 * @return
	 */
	@Select("select * from Ymt_SellerBrandApply where EnName = #{brandEn}")
	public List<Map<String,Object>> selectApplyBrandsByBrandEn(@Param("brandEn")String brandEn);
	
	@Select("select * from Ymt_ProductBrand where dUpdateTime>#{selectDate}")
	public List<Map<String,Object>> selectAllBrandsAfterSelectDate(@Param("selectDate")String selectDate);
	
	@Select("SELECT distinct cp.sProductProperty FROM Ymt_Products p INNER LOOP JOIN Ymt_Catalogs c  ON p.sProductId = c.sProductId "
			+ "INNER LOOP JOIN Ymt_CatalogProperty cp ON c.sCatalogId = cp.sCatalogId WHERE  p.iUserId=#{sellerId} and p.dLastUpdate > DATEADD(DAY,-30,GETDATE())  ")
	public List<Map<String,Object>> selectSpecificationsByUserId(@Param("sellerId")int sellerId);
	
	@Update("update YMT_SellerLoginLimitAutoOnShevel set ProcessStatus= #{ProcessStatus}  where SellerLoginLimitAutoOnShevelId = #{SellerLoginLimitAutoOnShevelId}")
	public int updateSellerLoginLimitAutoOnShevelAsSellerLoginLimitAutoOnShevelId(@Param("SellerLoginLimitAutoOnShevelId")String SellerLoginLimitAutoOnShevelId,@Param("ProcessStatus")String ProcessStatus);
	@Select("select * from  YMT_SellerLoginLimitAutoOnShevel where userid=#{userid} order by StartTime desc")
	public List<Map<String,Object>> selectSellerLoginLimitAutoOnShevelByUserId(@Param("userid")int userid);
	
	@Update("update Ymt_Products set IsPspProduct = #{psp} where sProductId =#{productId}")
	public int updatePspByProductId(@Param("productId")String productId,@Param("psp")boolean psp);
	
	@Update("update Ymt_Products set isNew = 1, dNewEndtime = dateadd(dd,1,GETDATE()), dListingTime = dateadd(dd,-1,GETDATE()) where sProductId =#{productId}")
	public int updateIsNewByProductId(@Param("productId")String productId);

	@Update("update Ymt_Products set ProductType = 3 where sProductId =#{productId}")
	public int updateProductTypeByProductId(@Param("productId")String productId);

	@Update("update Ymt_Catalogs set ParentCatalogId = #{parentCatalogId},RootCatalogId = #{rootCatalogId} where sCatalogId =#{catalogId}")
	public int updateCatalogsIdByCatalogId(@Param("catalogId")String catalogId,@Param("parentCatalogId")String parentCatalogId,@Param("rootCatalogId")String rootCatalogId);
	
	//数据过多清理数据 update Ymt_Products set Ymt_Products.iAction=-1 where sProductid  in (select  top 10000 sProductid from  dbo.Ymt_Products WHERE iUserId = 24636166 and iaction=0  and sProduct like '自动化%')
	@Update("UPDATE dbo.Ymt_Products SET validEnd = GETDATE() - 3 WHERE  sProductid  in (select  sProductid from  dbo.Ymt_Products WHERE iUserId = #{sellerId}  and iaction=0 )")
	public int updateProductoffSale(@Param("sellerId")int sellerId);
	
//	@Select(" select sproductid from dbo.Ymt_Products WHERE iUserId = #{sellerId} and iaction=0")
//	public  List<String> selectProductoffSale(@Param("sellerId")int sellerId);
//	@Update("UPDATE dbo.Ymt_Products SET validEnd = GETDATE() - 3 WHERE sproductid = #{sproductid} ")
//	public int updateProductoffSaleByProductId(@Param("sproductid")String sproductid);
	
	@Insert("insert into  Ymt_ProductPspInfo values (#{sellerId},#{productId},1,0,GETDATE(),GETDATE())")
	public int insertProductInfo(@Param("productId")String productId,@Param("sellerId")int sellerId);

	@Insert("insert into  Ymt_ProductPspInfo values (#{sellerId},#{productId},4,0,GETDATE(),GETDATE())")
	public int insertProductInfoAs4(@Param("productId")String productId,@Param("sellerId")int sellerId);
	
	@Delete("delete from Ymt_ProductDeliveryTemplate where SellerId = #{sellerId} and TemplateId = #{templateId}")
	public int deleteDeliveryTemplateBySellerIdAndTemplateId(@Param("sellerId")int sellerId,@Param("templateId")int templateId);
	
	
	@Delete("delete  from YMT_SellerLoginLimitAutoOnShevel where userId = #{userId}")
	public int deleteSellerLoginInfoByUserId(@Param("userId")int userId);


	@Select("select count(*) from ymt_AgentProducts where Status = #{status} and UserID = #{userId}")
	public int selectAgentProductNumByStatus(@Param("status") int status,@Param("userId") int userid);

	@Select(" select ap.*,p1.sproduct as title,cp.icatalogStatus as catalogStatus,cp.cardInfo,p1.sbrand as brandName,t.iCategoryId as thirdCategoryId,t.sCategory as thirdCategoryName,p1.remark,\n" +
			"               s.iCategoryId as secondCategoryId,s.sCategory as secondCategoryName,f.iCategoryId as firstCategoryId,f.sCategory as firstCategoryName\n" +
			"        from ymt_AgentProducts ap  WITH(NOLOCK) inner join Ymt_Products p1  WITH(NOLOCK)  on ap.ProductId = p1.sProductId\n" +
			"         LEFT JOIN dbo.Ymt_ProductCategory t ON p1.iThirdCategoryId = t.iCategoryId and p1.iThirdCategoryId > 0\n" +
			"        LEFT JOIN dbo.Ymt_ProductCategory s ON t.iMasterCategory = s.iCategoryId and t.iMasterCategory is not null\n" +
			"        LEFT JOIN dbo.Ymt_ProductCategory f ON s.iMasterCategory = f.iCategoryId  and s.iMasterCategory is not null\n" +
			"        INNER JOIN Ymt_Products  cp WITH(NOLOCK) on cp.sProductId = ap.RootProductId\n" +
			"        where ap.userId = ${userId} and ap.productId = #{productId}")
	public AgentProductComplexInfo getAgentProductDetails(@Param("userId")int userId, @Param("productId")String productId);

	/**
	 * 指定商品返佣
	 * @param example
	 * @return
	 */
	@SelectProvider(type=Ymt_AgentRebateSqlProvider.class, method="selectByExample")
    List<Ymt_AgentRebate> selectYmtAgentRebateExample(Ymt_AgentRebateExample example);
    /**
	 * 指定商品返佣 返佣条件
	 * @param example
	 * @return
	 */
    @SelectProvider(type=Ymt_ConditionRebateSqlProvider.class, method="selectByExample")
    List<Ymt_ConditionRebate> selectYmtConditionRebateExample(Ymt_ConditionRebateExample example);
    /**
	 * 指定商品返佣 返佣条件值
	 * @param example
	 * @return
	 */
    @SelectProvider(type=Ymt_ConditionRebateValueSqlProvider.class, method="selectByExample")
    List<Ymt_ConditionRebateValue> selectYmtConditionRebateValueExample(Ymt_ConditionRebateValueExample example);
    /**
	 * 指定商品返佣 返佣条件类型
	 * @param example
	 * @return
	 */
    @SelectProvider(type=Ymt_ConditionTypeSqlProvider.class, method="selectByExample")
    List<Ymt_ConditionType> selectYmtConditionTypeExample(Ymt_ConditionTypeExample example);
    
    /**
     * 神器指定输入下级商品条件
     * @param example
     * @return
     */
    @SelectProvider(type=Ymt_ProductSelectionConditionSqlProvider.class, method="selectByExample")
    List<Ymt_ProductSelectionCondition> selectYmtProductSelectionConditionExample(Ymt_ProductSelectionConditionExample example);
	public static void main(String args[]){
		Ymt_AgentRebateExample example=new Ymt_AgentRebateExample();
		example.createCriteria().andAgentRebateIDEqualTo(61);
		List<Ymt_AgentRebate> agnetrebates = SqlMapperFactory.getIntergratedProductMapper().selectYmtAgentRebateExample(example);
		System.out.println(FormatUtil.beanToGSONString(agnetrebates));
		
		Ymt_ConditionRebateExample example2=new Ymt_ConditionRebateExample();
		example2.createCriteria().andConditionRebateIdEqualTo(4);
		List<Ymt_ConditionRebate> ConditionRebate = SqlMapperFactory.getIntergratedProductMapper().selectYmtConditionRebateExample(example2);
		System.out.println(FormatUtil.beanToGSONString(ConditionRebate));
		
		Ymt_ConditionRebateValueExample example3=new Ymt_ConditionRebateValueExample();
		example3.createCriteria().andConditionRebateIdEqualTo(4);
		 List<Ymt_ConditionRebateValue> ConditionRebateValue = SqlMapperFactory.getIntergratedProductMapper().selectYmtConditionRebateValueExample(example3);
		System.out.println(FormatUtil.beanToGSONString(ConditionRebateValue));
		
		Ymt_ConditionTypeExample example4=new Ymt_ConditionTypeExample();
		example4.createCriteria().andConditionTypeIdEqualTo(2);
		List<Ymt_ConditionType> ConditionRebateType = SqlMapperFactory.getIntergratedProductMapper().selectYmtConditionTypeExample(example4);
		System.out.println(FormatUtil.beanToGSONString(ConditionRebateType));
		
		
		Ymt_ProductSelectionConditionExample example5=new Ymt_ProductSelectionConditionExample();
		example5.createCriteria().andUserIdEqualTo(500945639);
		List<Ymt_ProductSelectionCondition> ct = SqlMapperFactory.getIntergratedProductMapper().selectYmtProductSelectionConditionExample(example5);
		System.out.println(FormatUtil.beanToGSONString(ct));
	}
}






